set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=200;
set hive.exec.max.dynamic.partitions.pernode=200;

insert overwrite table jms_dm.dm_waybill_false_taking_dt
select
     a.taking_date                 as taking_date                    --揽收日期
    ,a.waybill                     as waybill_no                     --运单号
    ,a.is_interrupted              as is_break                       --是否截单0否1是2其他
    ,a.truncated_time              as break_time                     --截单时间
    ,a.is_diff_proxy               as is_span_agent                  --是否跨代理区交件
    ,a.is_arr_collect_intime       as is_entrepot_intime             --到达集散是否准点
    ,a.is_arr_center_intime        as is_center_intime               --到达中心是否准点
    ,a.is_intime                   as is_intime                      --时效是否准点:1是0否
    ,a.is_over                     as is_over                        --拆分责任标识
    ,a.hand_over_intime            as is_handover_intime             --交接是否准点
    ,a.hand_over_responsible_code  as handover_site_code             --交接不准点责任所属机构编码
    ,a.hand_over_responsible       as handover_site_name             --交接不准点责任所属机构名称
    ,a.goods_type_code             as goods_type_code                --物品类型编码
    ,a.goods_type_name             as goods_type_name                --物品类型名称
    ,a.customer_code               as customer_code                  --客户编号
    ,a.order_source                as order_source_code              --订单来源1紫金山2逍遥峰3桃花岛4其他
    ,a.type                        as type                           --类型,1:无三段码2:无路由3:错发4规划问题5正常6其他
    ,a.total_days_t                as total_days_t                   --T+x时效
    ,a.receive_network_code        as receive_network_code           --收件网点编码
    ,a.receive_network_name        as receive_network_name           --收件网点名称
    ,a.receive_franchisee_code     as receive_fran_code              --收件加盟商编码
    ,a.receive_franchisee          as receive_fran_name              --收件加盟商名称
    ,a.receive_proxy_code          as receive_agent_code             --收件代理区编码
    ,a.receive_proxy               as receive_agent_name             --收件代理区名称
    ,a.region_code                 as receive_manage_code            --收件管理大区编码
    ,a.region_name                 as receive_manage_name            --收件管理大区名称
    ,a.receive_city_id             as receive_city_code              --收件城市编码
    ,a.receive_city                as receive_city_name              --收件城市名称
    ,a.actual_collect_time         as first_taking_scan_time         --实际收件时间
    ,a.actual_send_time            as first_network_send_time        --网点实际发件时间
    ,a.actual_collect_code         as first_entrepot_actual_code     --实际交件集散点编码
    ,a.actual_collect_name         as first_entrepot_actual_name     --实际交件集散点名称
    ,a.actual_collect_arr_time     as first_entrepot_actual_time     --实际交件集散点时间
    ,a.plan_collect_code           as first_entrepot_plan_code       --规划到达集散点编码
    ,a.plan_collect_name           as first_entrepot_plan_name       --规划到达集散点名称
    ,a.plan_collect_arr_time       as first_entrepot_plan_time       --规划到达集散点时间
    ,a.actual_center_code          as first_actual_center_code       --实际交件转运中心编码
    ,a.actual_center_name          as first_center_actual_name       --实际交件转运中心名称
    ,a.actual_center_arr_time      as first_center_actual_time       --实际首中心到车时间
    ,null                          as first_center_shipment_no       --实际首中心到件任务号
    ,a.plan_center_arr_time        as first_center_plan_time         --规划首中心到车时间
    ,a.plan_arr_date               as first_center_plan_date         --规划到件日期
    ,a.hand_over_latest            as first_center_last_plan_time    --交接最晚到车时间(中心规划到达时间)
    ,a.center_planned_departure    as first_center_departure_time    --中心规划发车时间-1小时
    ,a.collect_proxy_code          as first_center_agent_code        --交件代理区编码
    ,a.collect_proxy               as first_center_agent_name        --交件代理区名称
    ,a.end_code                    as last_network_code              --目的网点编码
    ,a.end_name                    as last_network_name              --目的网点名称
    ,a.customer_name               as customer_name                  --客户名称
    ,case when b.waybill_no is not null then 1 else 0 end as if_center_arrival --是否中心到件
    ,dim_network.virt_code         as receive_virt_agent_code        --收件虚拟代理区编码
    ,dim_network.virt_name         as receive_virt_agent_name        --收件虚拟代理区编码
    ,a.taking_user_code            as taking_user_code               --揽收业务员
    ,a.taking_user_name            as taking_user_name               --揽收业务员
    ,case when a.actual_send_time is not null and a.actual_center_code is null and a.actual_collect_code is null then 1
          when a.actual_send_time is null then 0
     end                           as false_taking_type -- 虚假揽收类型 0未发件 1未到件
    ,a.dt                          as dt                             --分区日期
from
    jms_dm.dm_waybill_collect_intime a
left join (
    select *
    from(
        select
             waybill_no
            ,scan_site_code
            ,scan_time
            ,row_number() over(partition by waybill_no order by scan_time) as rn
        from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
          and scan_time between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
    ) a where a.rn = 1
) b on a.waybill = b.waybill_no
   and a.actual_collect_time < b.scan_time
left join
    jms_dim.dim_network_whole_massage  dim_network
        on a.receive_network_code = dim_network.code
where a.dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
and a.is_over is not null
and a.type = 0
-- 全部快件，自揽收时刻起，36小时内无集散点/转运中心到发件扫描记录则判定为虚假揽收
and
    case
        when a.actual_center_code is null and a.actual_collect_code is null then true
        when coalesce(a.actual_collect_arr_time, a.actual_center_arr_time) is not null
             and unix_timestamp(least(nvl(a.actual_collect_arr_time, '9999-01-01 00:00:00'), nvl(a.actual_center_arr_time, '9999-01-01 00:00:00')))
                 - unix_timestamp(a.actual_collect_time) >= 36 * 3600 then true
    end
distribute by dt
;